USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[ClassesTaught]    Script Date: 7/8/2013 7:20:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/* This query is used to pull data for the TestReportsTable.html file. 
it requires a userid that is usually supplied by the login information of the user.
The hard coded userID below is Brad Peterson's
https://chitester1dev.weber.edu:6838/misc/weber/csevals/TestReportsTable.html
https://chitester1dev.weber.edu:6838/misc/weber/csevals/MyReports.cfm
*/

ALTER PROCEDURE [dbo].[MyReports] @InstructorID int
AS
DECLARE @userID = 887969243
DECLARE @tenured INT = 
(
	SELECT max(CASE WHEN R.name IN('Tenure') THEN 1 else 0 END)
	FROM
		Users U
		JOIN UserRoles UR ON UR.userID=U.ID
		JOIN Roles R ON R.ID=UR.roleID
	WHERE R.name IN ('Tenure')
		AND (UR.revoked IS NULL or UR.revoked = 1900-1-1)
		AND U.id = @userID
);
WITH cteMain AS
(
	SELECT
		bannerCRN,
		CASE 	
			WHEN semester = 3 THEN 'Spring'
			WHEN semester = 2 THEN 'Fall'
			WHEN semester = 1 THEN 'Summer' END AS Semester,
		CASE 	
			WHEN semester = 3 THEN year
			else year - 1 END AS Year,
		CASE	
			WHEN semester = 3 THEN 3
			WHEN semester = 1 THEN 2
			WHEN semester = 2 THEN 1 END AS semesterOrder,
		semester AS dbSemester,
		year AS dbYear,
		CASE WHEN @tenured = 1 THEN 0 else 1 END AS shareDeansDisabled,
		SubjectCode,
		CourseNumber,
		ST.instructorID,
		R.studentID,
		CASE
			WHEN SE.ShareWithChairsDeans is null or SE.ShareWithChairsDeans = 'false' THEN 0
			ELSE 1 END AS ShareWithChairsDeans,
		CASE
			WHEN SE.ShareWithStudents is null or SE.ShareWithStudents = 'false' THEN 0
			ELSE 1 END AS ShareWithStudents,
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0 
		END Score,
		T.ID testID,
		T.testName 
		--SELECT * 
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND ST.instructorID = @userID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
		INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
		INNER JOIN Questions Q ON Q.ID = QR.questionID 
		INNER JOIN Tests T ON T.ID = Q.testID 
			AND T.typeID = 5 --Course Eval
		INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
		INNER JOIN Distractors D ON D.ID = DR.distractorID
		LEFT JOIN ShareEvals SE ON SE.InstructorID = ST.InstructorID
)


SELECT subjectCode + ' ' + CourseNumber + ' ' + cast(Semester AS varchar(20)) + ' ' + cast(year AS varchar(20)) + ' ' + cast(bannerCRN AS varchar(20)) AS Link,
	bannerCRN,
	Semester,
	Year,
	dbYear,
	dbSemester,
	SubjectCode,
	CourseNumber,
	ShareDeansDisabled,
	ShareWithChairsDeans,
	ShareWithStudents,
	AVG(score) score,
	COUNT(DISTINCT studentID) students,
	STDEVP(score) standardDeviation
FROM 
	cteMain
GROUP BY
	bannerCRN,
	Semester,
	Year,
	dbYear,
	dbSemester,
	SubjectCode,
	CourseNumber,
	instructorID,
	testName,
	ShareDeansDisabled,
	ShareWithChairsDeans,
	ShareWithStudents
	group by semesterOrder
ORDER BY
	year DESC, semesterOrder


GO